/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package orest.common.dao;
import java.sql.*;
import orest.common.response.*;
import orest.common.util.*;
import java.util.ArrayList;
import orest.common.valueobject.KjcgVal;
/**
 *
 * @author admin
 */
public class KjcgDao {
    
    public KjcgDao() {
    }

   

     public boolean findKjcgByPrk(Response rp,String id) {
       boolean result = true;
        ArrayList al = new ArrayList();

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select KjcgID,UnitID,Kjcg_Item_3,Kjcg_Item_120, Kjcg_Item_9,Kjcg_Item_cglb,Kjcg_Item_13,Kjcg_Item_13_1,Kjcg_Item_121,Kjcg_Item_8,Kjcg_Item_210," +
                "Kjcg_Item_123,Kjcg_Item_16,Kjcg_Item_125_1,Kjcg_Item_2,Kjcg_Item_201 ,Kjcg_Item_41,Kjcg_Item_41_2,AchiveType from  T_Kjcg where  KjcgID=?";// + id;
       

        /* sql string end */


        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery ();
       
            while(rs.next ()){
                 al.add (new KjcgVal(
                                     rs.getString ("KjcgID"),                 
                                     rs.getString("UnitID"),
                                     rs.getString("Kjcg_Item_3"),
                                     rs.getString("Kjcg_Item_120"),
                                     rs.getDate("Kjcg_Item_121"),
                                     rs.getString("Kjcg_Item_123"),
                                     rs.getString("Kjcg_Item_16"),
                                     rs.getString("Kjcg_Item_125_1"),
                                     rs.getString("Kjcg_Item_13"),
                                     rs.getString("Kjcg_Item_13_1"),
                                     rs.getString("Kjcg_Item_2"),
                                     rs.getString("Kjcg_Item_210"),
                                     rs.getString("Kjcg_Item_8"),
                                     rs.getString("Kjcg_Item_201"),
                                     rs.getString("Kjcg_Item_41"),
                                     rs.getString("Kjcg_Item_41_2"),
                                     rs.getString("Kjcg_Item_cglb"),
                                     rs.getString("Kjcg_Item_9"),
                                     rs.getString("AchiveType")
                                    ));                           
             
            }
      
            rp.setValObjs (al);         
            return result;
          
        }
       catch(SQLException e){
            e.printStackTrace();
            result = false;
            return result;
        }
        catch(Exception e){
            e.printStackTrace();
            result = false;
            return result;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                	e.printStackTrace();
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                	e.printStackTrace();
                }
            }
        }
    }

      public boolean findUnitIDByKjcgID(Response rp,String id) {
       boolean result = true;
        ArrayList al = new ArrayList();

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select UnitID from  T_Kjcg where  KjcgID=" + id;


        /* sql string end */


        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                KjcgVal KjcgVal = new KjcgVal();
                KjcgVal.setUnitID(rs.getString("UnitID"));
                al.add (KjcgVal);

            }
            rp.setValObjs (al);
            return result;

        }
       catch(SQLException e){
            e.printStackTrace();
            result = false;
            return result;
        }
        catch(Exception e){
            e.printStackTrace();
            result = false;
            return result;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                	e.printStackTrace();
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                	e.printStackTrace();
                }
            }
        }
    }


      public boolean findKjcgByPrk_W(Response rp,String id) {
       boolean result = true;
        ArrayList al = new ArrayList();

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select *  from  T_Kjcg where  KjcgID=" + id;

// System.out.println(sql);
        /* sql string end */


        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                 al.add (new KjcgVal(
                        rs.getString ("KjcgID"),
                        rs.getString ("UnitID"),
                        rs.getString ("TopicWords"),
                        rs.getString ("Kjcg_Item_1"),
                        rs.getString ("Kjcg_Item_110"),
                        rs.getString ("Kjcg_Item_12"),
                        rs.getString ("Kjcg_Item_12_1"),
                        rs.getString ("Kjcg_Item_Year"),
                        rs.getString ("Kjcg_Item_120"),
                        rs.getDate ("Kjcg_Item_121"),
                        rs.getString ("Kjcg_Item_123"),
                        rs.getString ("Kjcg_Item_124_1"),
                        rs.getString ("Kjcg_Item_125"),
                        rs.getString ("Kjcg_Item_125_1"),
                        rs.getString ("Kjcg_Item_126"),
                        rs.getString ("Kjcg_Item_13"),
                        rs.getString ("Kjcg_Item_13_1"),
                        rs.getString ("Kjcg_Item_13_2"),
                        rs.getString ("Kjcg_Item_13_3"),
                        rs.getString ("Kjcg_Item_16"),
                        rs.getString ("Kjcg_Item_2"),
                        rs.getString ("Kjcg_Item_200"),
                        rs.getString ("Kjcg_Item_210"),
                        rs.getString ("Kjcg_Item_3"),
                        rs.getString ("Kjcg_Item_33"),
                        rs.getString ("Kjcg_Item_4"),
                        rs.getString ("Kjcg_Item_8"),
                        rs.getString ("Kjcg_Item_127"),
                        rs.getString ("Kjcg_Item_127_1"),
                        rs.getString ("Kjcg_Item_201"),
                        rs.getString ("Kjcg_Item_41"),
                        rs.getString ("Kjcg_Item_41_1"),
                        rs.getString ("Kjcg_Item_41_2"),
                        rs.getString ("Kjcg_Item_61"),
                        rs.getString ("Kjcg_Item_32"),
                        rs.getString ("Kjcg_Item_32_1"),
                        rs.getString ("Kjcg_Item_34"),
                        rs.getInt ("Kjcg_Item_45"),
                        rs.getString ("Kjcg_Item_46"),
                        rs.getString ("Kjcg_Item_47"),
                        rs.getString ("AchieveType"),
                        rs.getString ("Kjcg_Item_301"),
                        rs.getString ("Kjcg_Item_302"),
                        rs.getString ("Kjcg_Item_303"),
                        rs.getString ("Kjcg_Item_161"),
                        rs.getDouble ("Kjcg_Item_151"),
                        rs.getDouble ("Kjcg_Item_152"),
                        rs.getDouble ("Kjcg_Item_153"),
                        rs.getDouble ("Kjcg_Item_150"),
                        rs.getString ("Kjcg_Item_140"),
                        rs.getDouble ("Kjcg_Item_142"),
                        rs.getDouble ("Kjcg_Item_143"),
                        rs.getDouble ("Kjcg_Item_141"),
                        rs.getString ("Kjcg_Item_130"),
                        rs.getString ("Kjcg_Item_133"),
                        rs.getString ("Kjcg_Item_21"),
                        rs.getString ("Kjcg_Item_22"),
                        rs.getString ("Kjcg_Item_51"),
                        rs.getString ("Kjcg_Item_52"),
                        rs.getString ("Kjcg_Item_53"),
                        rs.getString ("Kjcg_Item_56"),
                        rs.getString ("Kjcg_Item_dqdm"),
                        rs.getString ("Kjcg_Item_cglb"),
                        rs.getDouble ("Kjcg_Item_144"),
                        rs.getDouble ("Kjcg_Item_145"),
                        rs.getDouble ("Kjcg_Item_149"),
                        rs.getDouble ("Kjcg_Item_154"),
                        rs.getString ("Kjcg_Item_128"),
                        rs.getString ("Kjcg_Item_15"),
                        rs.getString ("Kjcg_Item_15_1"),
                        rs.getDouble ("Kjcg_Item_142_1"),
                        rs.getDouble ("Kjcg_Item_142_2"),
                        rs.getDouble ("Kjcg_Item_142_3"),
                        rs.getDouble ("Kjcg_Item_148"),
                        rs.getDouble ("Kjcg_Item_243"),
                        rs.getDouble ("Kjcg_Item_244"),
                        rs.getString ("Kjcg_Item_51_1"),
                        rs.getLong ("Kjcg_Item_51_2"),
                        rs.getLong ("Kjcg_Item_51_3"),
                        rs.getDouble ("Kjcg_Item_151_1"),
                        rs.getDouble ("Kjcg_Item_152_1"),
                        rs.getString ("NewEnergyClass1"),
                        rs.getString ("NewEnergyClass2"),
                        rs.getString ("Ifzscqxs"),
                        rs.getString ("Transfercd"),
                        rs.getString ("Transferct"),
                        rs.getString ("CooperUnit"),
                        rs.getString ("CoCreator"),
                        rs.getString ("RecommDept"),
                        rs.getString ("Kjcg_Item_9"),
                        rs.getString ("Kjcg_Item_13_1s"),
                        rs.getString ("ChannelShow"),
                        rs.getString ("HomepageShow")
                                    ));

            }
            rp.setValObjs (al);
            return result;

        }
       catch(SQLException e){
            e.printStackTrace();
            result = false;
            return result;
        }
        catch(Exception e){
            e.printStackTrace();
            result = false;
            return result;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                	e.printStackTrace();
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                	e.printStackTrace();
                }
            }
        }
    }

   public boolean findKjcgByConditon(Response rp,String condtion,
                                                                int pageNo,
                                                                int pageRecord,
                                                                int total) {

        boolean result = true;
        ArrayList al = new ArrayList();
        int iPrePageCount ;
        int tempRow = 0;
        Tools tool = new Tools();

        /* number of record in page */
        int startRow = (pageNo -1) * pageRecord;
        int endRow = pageNo * pageRecord;

        if(endRow > total)
            endRow = total;




        tempRow = 1;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */ 
        String rowcount = "select top  " + Integer.toString(endRow);
        String sql = "  KjcgID,Kjcg_Item_3,UnitID,Kjcg_Item_9,Kjcg_Item_13_1s from  T_Kjcg where  " + condtion;


        	//原来的排序
            //sql += " order by Kjcg_Item_Year desc,substring(AchiveType,1,1) desc";
        	
        //改了之后的排序
        sql += " order by KJCG_ITEM_121 desc";
            
            
            
        /* sql string end */

        /*
           count plan production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                if(tempRow > startRow) {
                 KjcgVal KjcgVal = new KjcgVal();
                 KjcgVal.setKjcgID(rs.getString("KjcgID"));
                 KjcgVal.setKjcg_Item_3(rs.getString("Kjcg_Item_3"));
                 KjcgVal.setUnitID(rs.getString("UnitID"));
                 KjcgVal.setKjcg_Item_9(rs.getString("Kjcg_Item_9"));
                 KjcgVal.setKjcg_Item_13_1s(rs.getString("Kjcg_Item_13_1s"));
                 al.add (KjcgVal);
                }

                else
                    tempRow++;
            }


                rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("When find the kjcg info, have a exception!" +
                                " The class name is KjcgDao." + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getMyConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is KjcgDao and the " +
                                 "function is findKjcgByCondition! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                 
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of KjcgDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of KjcgDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of KjcgDao and" +
                            " the function is findKjcgByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }


    public boolean findKjcgShowByConditon(Response rp,String condtion,
                                                                int pageNo,
                                                                int pageRecord,
                                                                int total) {

        boolean result = true;
        ArrayList al = new ArrayList();
        int iPrePageCount ;
        int tempRow = 0;
        Tools tool = new Tools();

        /* number of record in page */
        int startRow = (pageNo -1) * pageRecord;
        int endRow = pageNo * pageRecord;

        if(endRow > total)
            endRow = total;




        tempRow = 1;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String rowcount = "select top  " + Integer.toString(endRow);
        String sql = "  KjcgID,Kjcg_Item_3,UnitID,Kjcg_Item_9,Kjcg_Item_Year,ChannelShow,HomepageShow from  T_Kjcg where  " + condtion;



            sql += " order by Kjcg_Item_Year desc";
        /* sql string end */

        /*
           count plan production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                if(tempRow > startRow) {
                 KjcgVal KjcgVal = new KjcgVal();
                 KjcgVal.setKjcgID(rs.getString("KjcgID"));
                 KjcgVal.setKjcg_Item_3(rs.getString("Kjcg_Item_3"));
                 KjcgVal.setUnitID(rs.getString("UnitID"));
                 KjcgVal.setKjcg_Item_9(rs.getString("Kjcg_Item_9"));
                 KjcgVal.setKjcg_Item_Year(rs.getString("Kjcg_Item_Year"));
                 KjcgVal.setChannelShow(rs.getString("ChannelShow"));
                 KjcgVal.setHomepageShow(rs.getString("HomepageShow"));
                 al.add (KjcgVal);
                }

                else
                    tempRow++;
            }


                rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("When find the kjcg info, have a exception!" +
                                " The class name is KjcgDao." + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getMyConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is KjcgDao and the " +
                                 "function is findKjcgShowByCondition! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{

                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of KjcgDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of KjcgDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of KjcgDao and" +
                            " the function is findKjcgShowByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }


     public long countKjcgByCondition(String condtion, Response rp) {

        ArrayList al = new ArrayList();
        long total = 0;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select count(1) as cnt from  T_Kjcg where  " + condtion;
        /* sql string end */


        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                total = rs.getLong("cnt");
            }
            return total;
        }
        catch(SQLException e){
            System.out.println("When countKjcgByCondition, have a exception!" +
                                " The class name is KjcgDao." + e);
            return 0;
        }
        catch(Exception e){
            System.out.println ("When call the countKjcgByCondition, have a"+
                                 "exception occured!   ");
            e.printStackTrace();

            return 0;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of KjcgDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of KjcgDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of KjcgDao and" +
                            " the function is countKjcgByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }


        public boolean findTopKjcgByCondition(Response rp,String num,String con,String sorttype) {

        boolean result = true;
        ArrayList al = new ArrayList();
        Tools tool = new Tools();



        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String rowcount = "select top  " + num;
        String sql = "KjcgID,Kjcg_Item_3 from T_Kjcg where " + con ;
        
        if (sorttype.equals("r"))
                sql+=" order by ChannelShow desc,Kjcg_Item_Year desc,substring(AchiveType,1,1) desc ";
        else
                sql+=" order by Kjcg_Item_Year desc,substring(AchiveType,1,1) desc ";

        /* sql string end */


        try{

            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                 KjcgVal KjcgVal = new KjcgVal();
                 KjcgVal.setKjcgID(rs.getString("KjcgID"));
                 KjcgVal.setKjcg_Item_3(rs.getString("Kjcg_Item_3"));
                 al.add (KjcgVal);
            }


                rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("When get the kjcg info, have a exception!" +
                                " The class name is KjcgDao." + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is KjcgDao and the " +
                                 "function is findTopKjcgByCondition! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{

                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of KjcgDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of KjcgDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of KjcgDao and" +
                            " the function is findTopKjcgByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }


  public boolean setChannelShow(String kjcgid,String ifshow){
    	boolean result = true;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();

        String sql = "";
        try{
                   sql = " update  T_Kjcg set ";
                   sql += "ChannelShow = '"+ ifshow +"' ";
                  sql += "where KjcgID = '" + kjcgid + "'";
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            pstmt.execute();
            return result;
        }
        catch(SQLException e){
            e.printStackTrace();
            result = false;
            return result;
        }
        catch(Exception e){
            e.printStackTrace();
            result = false;
            return result;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                	e.printStackTrace();
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                	e.printStackTrace();
                }
            }
        }
    }


   public boolean SetChannelShow_Batch(String ifshow,String[] id) {
        boolean flag = false;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();


        String sql = "update T_Kjcg set ChannelShow='"+ifshow +"' where KjcgID=?";

       try {
            conn = db.getSysConnectionPool ();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < id.length; i++) {
                pstmt.setString(1, id[i]);
                // ʹ������
                pstmt.addBatch();
             }
            // ִ������
           int[] result = pstmt.executeBatch();
           if (result[0] > 0) {
                flag = true;

            }
                return flag;
            }
            catch(SQLException e){
                e.printStackTrace();
                flag = false;
                return flag;
            }
            catch(Exception e){
                e.printStackTrace();
                flag = false;
                return flag;
            }
            finally {
                if(null != pstmt){
                    try{
                        pstmt.close ();
                        pstmt = null;
                    }
                    catch(SQLException e){
                        e.printStackTrace();
                     }
                    }

                     /* release the conn */
                     if(null != conn){
                     try{
                        conn.close();
                        conn = null;
                         db = null;
                        }
                        catch(Exception e){
                            e.printStackTrace();
                        }
                    }
                }


 }

}
